package leeon.nlzb;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class Nlzb {
	
	private static Log logger = LogFactory.getLog(Nlzb.class);
	
	public static final String BASE_DIR = "D:\\project\\eurospace\\forpeddy\\misc\\list.xls";
	
	public static final int F_WORKSHEET = 0;
	public static final int S_WORKSHEET = 1;
	
	public static final int F_START_ROW = 3;
	public static final int F_CODE_COL = 0;
	public static final int F_VALUE_COL = 4;
	
	public static final int S_START_ROW = 1;
	public static final int S_CODE_COL = 2;
	public static final int S_VALUE_COL = 6;
	
	public static final int DIF_VALUE = 2;
	public static final String BATCH = "200712";//(new SimpleDateFormat("yyyyMM")).format(new Date());
	
	public static Map<String, String> SYS_EMPTY_ENABLE = new HashMap<String, String>();
	static {
		SYS_EMPTY_ENABLE.put("BF1110", "");
		SYS_EMPTY_ENABLE.put("BF1120", "");
		SYS_EMPTY_ENABLE.put("BF1121", "");
		SYS_EMPTY_ENABLE.put("BF1122", "");
		SYS_EMPTY_ENABLE.put("BF1210", "");
		SYS_EMPTY_ENABLE.put("BF1220", "");
		SYS_EMPTY_ENABLE.put("BF1221", "");
		SYS_EMPTY_ENABLE.put("BF1222", "");
		SYS_EMPTY_ENABLE.put("BF1310", "");
		SYS_EMPTY_ENABLE.put("BF1320", "");
		SYS_EMPTY_ENABLE.put("BF1321", "");
		SYS_EMPTY_ENABLE.put("BF1322", "");
		SYS_EMPTY_ENABLE.put("BF1410", "");
		SYS_EMPTY_ENABLE.put("BF1420", "");
		SYS_EMPTY_ENABLE.put("BF1510", "");
		SYS_EMPTY_ENABLE.put("BF1520", "");
		SYS_EMPTY_ENABLE.put("BF1521", "");
		SYS_EMPTY_ENABLE.put("BF1522", "");
	}
	
	public static Map<String, String> SYS_DIF_ENABLE = new HashMap<String, String>();
	static {
		SYS_DIF_ENABLE.put("BR1000", "");
		SYS_DIF_ENABLE.put("BR1200", "");
		SYS_DIF_ENABLE.put("BR1010", "");
		SYS_DIF_ENABLE.put("BR1210", "");
		
		SYS_DIF_ENABLE.put("BR2000", "");
		SYS_DIF_ENABLE.put("BR2001", "");
		
		SYS_DIF_ENABLE.put("BR1977", "");
		SYS_DIF_ENABLE.put("BR1978", "");
		
		SYS_DIF_ENABLE.put("BN1100", "");
	}
	
	public static Map<String, String> SYS_JH = new HashMap<String, String>();
	static {
		SYS_JH.put("BR1300", "");
		SYS_JH.put("BR1400", "");
		SYS_JH.put("BR1320", "");
		SYS_JH.put("BR1420", "");
	}
	
	public static Map<String, String> SYS_KD = new HashMap<String, String>();
	static {
		SYS_KD.put("BR2110", "");
		SYS_KD.put("BR2111", "");
		SYS_KD.put("BR2120", "");
		SYS_KD.put("BR2121", "");
		SYS_KD.put("BR2130", "");
		SYS_KD.put("BR2131", "");
		SYS_KD.put("BR2140", "");
		SYS_KD.put("BR2141", "");
	}
	
	public static Map<String, String> SYS_WX = new HashMap<String, String>();
	static {
		SYS_WX.put("BR1970", "");
		SYS_WX.put("BR1971", "");
	}
	
	public static Map<String, String> SYS_SJ = new HashMap<String, String>();
	static {
		SYS_SJ.put("BN1110", "");
		SYS_SJ.put("BN1120", "");
		SYS_SJ.put("BN1130", "");
		SYS_SJ.put("BN1140", "");
	}
	
	private Map<String, Float> fMap = new HashMap<String, Float>();
	private Map<String, Float> sMap = new HashMap<String, Float>();
	private Map<String, String> isManualMap = new HashMap<String, String>();
	private Set<String> modifiedCode = new HashSet<String>();
	
	public Nlzb() throws BiffException, IOException {
		File file = new File(BASE_DIR);
		Workbook workbook = Workbook.getWorkbook(file);
		parseFList(workbook);
		parseSList(workbook);
	}
	
	private void parseFList(Workbook workbook) {
		Sheet sheet = workbook.getSheet(F_WORKSHEET);
		int length = sheet.getColumn(0).length;
		for (int i = F_START_ROW; i < length; i ++) {
			String code = sheet.getCell(F_CODE_COL, i).getContents().trim();
			if (code == null || code.length() == 0) continue;
			
			String value = sheet.getCell(F_VALUE_COL, i).getContents().trim();			
			
			fMap.put(code, (value!=null&&value.length()!=0)?new Float(value):null);
		}
	}
	
	private void parseSList(Workbook workbook) {
		Sheet sheet = workbook.getSheet(S_WORKSHEET);
		int length = sheet.getColumn(0).length;
		for (int i = S_START_ROW; i < length; i ++) {
			String code = sheet.getCell(S_CODE_COL, i).getContents().trim();
			if (code == null || code.length() == 0) continue;
			
			String value = sheet.getCell(S_VALUE_COL, i).getContents().trim();			
			
			sMap.put(code, (value!=null&&value.length()!=0)?new Float(value):null);
			
			String manual = sheet.getCell(S_VALUE_COL-1, i).getContents().trim();
			if (manual != null && manual.length() != 0) isManualMap.put(code, "");
		}
	}
	
	public void compare() {
		Iterator<String> fi = fMap.keySet().iterator();
		while (fi.hasNext()) {
			String code = fi.next();
			if (!sMap.containsKey(code)) {
				continue;
			} else if (SYS_EMPTY_ENABLE.containsKey(code)) {
				continue;
			}
				
			Float fv = fMap.get(code);
			Float sv = sMap.get(code);
			
			if (fv == null) {
				logger.info("指标代码["+ code + "], 财务值为空");
				continue;
			} else if (sv == null) {
				logger.info("指标代码["+ code + "], 系统值为空");
				continue;
			}
			
			float f = fv.floatValue();
			float s = sv.floatValue();
			float d = Math.abs(f - s)*100/f;
			
			if (SYS_DIF_ENABLE.containsKey(code)) continue;
			
			if (d > DIF_VALUE) {
				logger.info("指标代码["+ code + "], 财务值[" + f +"], 系统值["+ s +"], 相差["+ d +"%]");
				modifyValue(code);
			}
		}
	}
	
	public void modifyValue(String code) {
		
		if (SYS_JH.containsKey(code)) {
			modifyValueJH(code);
		} else if (SYS_KD.containsKey(code)) {
			modifyValueKD(code);
		} else if (SYS_WX.containsKey(code)) {
			modifyValueWX(code);
		} else if (SYS_SJ.containsKey(code)) {
			modifyValueSJ(code);
		} else {
			modifyValueOther(code);
		}
		
	}
	
	private void modifyValueJH(String code) {
		float f = fMap.get(code);
		f = calculateModifyValue(f);
		sMap.put(code, f);
		
		sMap.put("BR1010", sMap.get("BR1300") + sMap.get("BR1400"));
		sMap.put("BR1210", sMap.get("BR1320") + sMap.get("BR1420"));
		sMap.put("BR1000", sMap.get("BR1010") + sMap.get("BR1977"));
		sMap.put("BR1200", sMap.get("BR1210") + sMap.get("BR1978"));
		
		modifiedCode.add(code);
		modifiedCode.add("BR1010");
		modifiedCode.add("BR1210");
		modifiedCode.add("BR1000");
		modifiedCode.add("BR1200");
		
		modifyLog(code, new String[]{"BR1010", "BR1210", "BR1000", "BR1200"});
	}
	
	private void modifyValueKD(String code) {
		float f = fMap.get(code);
		f = calculateModifyValue(f);
		sMap.put(code, f);
		
		sMap.put("BR2000", sMap.get("BR2110") + sMap.get("BR2120") + sMap.get("BR2130") + sMap.get("BR2140"));
		sMap.put("BR2001", sMap.get("BR2111") + sMap.get("BR2121") + sMap.get("BR2131") + sMap.get("BR2141"));		
		
		modifiedCode.add(code);
		modifiedCode.add("BR2000");
		modifiedCode.add("BR2001");
		
		modifyLog(code, new String[]{"BR2000", "BR2001"});
	}

	private void modifyValueWX(String code) {
		float f = fMap.get(code);
		f = calculateModifyValue(f);
		sMap.put(code, f);
		
		sMap.put("BR1977", sMap.get("BR1970"));
		sMap.put("BR1978", sMap.get("BR1971"));
		sMap.put("BR1000", sMap.get("BR1010") + sMap.get("BR1977"));
		sMap.put("BR1200", sMap.get("BR1210") + sMap.get("BR1978"));
		
		
		modifiedCode.add(code);
		modifiedCode.add("BR1977");
		modifiedCode.add("BR1978");
		modifiedCode.add("BR1000");
		modifiedCode.add("BR1200");
		
		modifyLog(code, new String[]{"BR1977", "BR1978", "BR1000", "BR1200"});
	}
	
	private void modifyValueSJ(String code) {
		float f = fMap.get(code);
		f = calculateModifyValue(f);
		sMap.put(code, f);
		
		sMap.put("BN1100", sMap.get("BN1110") + sMap.get("BN1120") + sMap.get("BN1130") + sMap.get("BN1140"));
		
		modifiedCode.add(code);
		modifiedCode.add("BN1100");
		
		modifyLog(code, new String[]{"BN1100"});
	}

	private void modifyValueOther(String code) {
		float f = fMap.get(code);
		f = calculateModifyValue(f);
		sMap.put(code, f);
		
		modifiedCode.add(code);
		
		modifyLog(code, null);
	}
	
	private float calculateModifyValue(float f) {
		return f + f*(float)0.001;
	}
	
	private void modifyLog(String code, String[] relationCode) {
		logger.info("指标代码["+ code + "], 系统值调整["+ sMap.get(code) +"]");
		if (relationCode == null) return;
			
		logger.info("联动调整:{");
		for (String c : relationCode)
			logger.info("	指标代码["+c+"], 指标值["+sMap.get(c)+"]");
		logger.info("}");
	}
	
	
	public void outputAll() {
		for (String code : modifiedCode) {
			outputOne(code, sMap.get(code).intValue(), isManualMap.containsKey(code));
		}
		
		System.out.println("");
		System.out.println("update PERF_VALUE SET STATUS = 1 WHERE BATCH = '" + BATCH + "';");
		System.out.println("commit;");
	}
	
	private void outputOne(String code, int value, boolean isManual) {
		String sql = null;
		if (isManual) {
			sql = "update PERF_VALUE SET MANUAL_VALUE  = " +
				value +
				", ORIGIN_VALUE = " +
				value +
				"  WHERE BATCH = '" +
				BATCH +
				"' AND CODE = '" +
				code +
				"';";
		} else {
			sql = "update PERF_VALUE SET SYS_VALUE = " +
			value +
			", ORIGIN_VALUE = " +
			value +
			"  WHERE BATCH = '" +
			BATCH +
			"' AND CODE = '" +
			code +
			"';";
		}
		System.out.println(sql);
	}
	
	public static void main(String[] args) throws BiffException, IOException {
		Nlzb nlzb = new Nlzb();
		nlzb.compare();
		nlzb.outputAll();

	}

}
